﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    public partial class ItemYearBal
    {

        public DataSet GetAllList(string where)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FItemName, FYear,FItemNo,FEntityNo,FPrice,FQty,FAmount from dbo.v_Item_YearBal");
            if (where != "") {
                strSql.Append(" where " + where);
            }

            return DbHelperSQL.Query(strSql.ToString());       
        }

        ///// <summary>
        ///// 增加一条数据
        ///// </summary>
        ///// <param name="model">model对象</param>
        ///// <returns>增加</returns>
        //public bool Add(Model.ItemYearBal model)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("insert into dbo.t_ItemYearBal(");
        //    strSql.Append("FYear,FItemNo,FEntityNo,FPrice,FQty,FAmount)");
        //    strSql.Append(" values (");
        //    strSql.Append("@FYear,@FItemNo,@FEntityNo,@FPrice,@FQty,@FAmount)");
        //    SqlParameter[] parameters = {
        //            new SqlParameter("@FYear", SqlDbType.Decimal,5),
        //            new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
        //            new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
        //            new SqlParameter("@FPrice", SqlDbType.Decimal,13),
        //            new SqlParameter("@FQty", SqlDbType.Decimal,13),
        //            new SqlParameter("@FAmount", SqlDbType.Decimal,13)};
        //    parameters[0].Value = model.FYear;
        //    parameters[1].Value = model.FItemNo;
        //    parameters[2].Value = model.FEntityNo;
        //    parameters[3].Value = model.FPrice;
        //    parameters[4].Value = model.FQty;
        //    parameters[5].Value = model.FAmount;

        //    int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        //    if (rows > 0)
        //    {
        //        return true;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}

        ///// <summary>
        ///// 是否存在该记录
        ///// </summary>
        ///// <param name="year">年度</param>
        ///// <param name="itemNo">用品编号</param>
        ///// <param name="entityNo">公司编号</param>
        ///// <returns>是否存在</returns>
        //public bool Exists(decimal year, string itemNo, string entityNo)
        //{
        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select count(1) from dbo.t_ItemYearBal");
        //    strSql.Append(" where FYear=@FYear and FItemNo=@FItemNo and FEntityNo=@FEntityNo ");
        //    SqlParameter[] parameters = {
        //            new SqlParameter("@FYear", SqlDbType.Decimal,5),
        //            new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
        //            new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
        //    parameters[0].Value = year;
        //    parameters[1].Value = itemNo;
        //    parameters[2].Value = entityNo;

        //    return DbHelperSQL.Exists(strSql.ToString(), parameters);
        //}


        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model">model对象</param>
        /// <returns>是否更新</returns>
        public void Update(Model.ItemYearBal itemYearBalModel, Model.Item itemModel)
        {
            StringBuilder strSql = new StringBuilder();
            CommandInfo cmds = new CommandInfo();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            strSql.Append("update dbo.t_Item set ");
            strSql.Append("FPrice=@FPrice,");
            strSql.Append("FQty=@FQty,");
            strSql.Append("FAmount=@FAmount");
            strSql.Append(" where FItemNo=@FItemNo and FEntityNo=@FEntityNo");
            SqlParameter[] parameters1 = {
					new SqlParameter("@FPrice", SqlDbType.Decimal,13),
					new SqlParameter("@FQty", SqlDbType.Decimal,13),
					new SqlParameter("@FAmount", SqlDbType.Decimal,13),
					new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)};
            parameters1[0].Value = itemModel.FPrice;
            parameters1[1].Value = itemModel.FQty;
            parameters1[2].Value = itemModel.FAmount;
            parameters1[3].Value = itemModel.FItemNo;
            parameters1[4].Value = itemModel.FEntityNo;


            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters1;
            cmdList.Add(cmds);
            


            strSql = new StringBuilder();
            cmds = new CommandInfo();

            strSql.Append("update dbo.t_ItemYearBal set ");
            strSql.Append("FPrice=@FPrice,");
            strSql.Append("FQty=@FQty,");
            strSql.Append("FAmount=@FAmount");
            strSql.Append(" where FYear=@FYear and FItemNo=@FItemNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FPrice", SqlDbType.Decimal,13),
					new SqlParameter("@FQty", SqlDbType.Decimal,13),
					new SqlParameter("@FAmount", SqlDbType.Decimal,13),
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FItemNo", SqlDbType.NVarChar,25),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)};
            parameters[0].Value = itemYearBalModel.FPrice;
            parameters[1].Value = itemYearBalModel.FQty;
            parameters[2].Value = itemYearBalModel.FAmount;
            parameters[3].Value = itemYearBalModel.FYear;
            parameters[4].Value = itemYearBalModel.FItemNo;
            parameters[5].Value = itemYearBalModel.FEntityNo;

            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);
            DbHelperSQL.ExecuteSqlTranWithString(cmdList);
        }

    }
}
